﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace eLoan.Models
{
    public static class FundingHelper
    {
        public static string GetNextLoanDocumentNo(DatabaseContext db, string loggedInUserBranchCode)
        {
            var yearSeries = loggedInUserBranchCode + "-" + DateTime.Now.Year.ToString() + "-";
            var series = db.Loans.Where(w => w.DocumentNo.Contains(yearSeries)).ToList();
            var lastSeries = string.Format("{0:0000000}", 1);

            if (series.Any())
            {
                lastSeries = series.OrderByDescending(o => o.DocumentNo).Select(s => s.DocumentNo).FirstOrDefault<string>();
                lastSeries = lastSeries.Replace(yearSeries, string.Empty);

                var nextSeries = int.Parse(lastSeries) + 1;

                lastSeries = string.Format("{0:0000000}", nextSeries);

            }

            return lastSeries = yearSeries + lastSeries;
        }

        private static LoanModel GetLoanModelDefaults(DatabaseContext db, string userName, string userBranchCode)
        {
            var loanModel = new LoanModel();
            loanModel.Loan = new Loan();

            loanModel.Loan.DocumentNo = GetNextLoanDocumentNo(db, userBranchCode);
            loanModel.Loan.DocumentDate = DateTime.Now;
            loanModel.Loan.BranchCode = userBranchCode;
            loanModel.BranchName = AdministrationHelper.GetBranchName(userBranchCode);
            loanModel.Loan.BorrowerID = 0;
            loanModel.Loan.AgentID = 0;
            loanModel.AgentName = string.Empty;
            loanModel.Loan.ProceedTypeID = (int)AdministrationHelper.ProceedType.Weekly;
            loanModel.ProceedTypeName = AdministrationHelper.ProceedType.Weekly.ToString();
            loanModel.Loan.LoanTypeID = 0;
            loanModel.LoanTypeName = string.Empty;
            loanModel.Loan.Term = 100;
            loanModel.Loan.CreditLimit = 0;
            loanModel.Loan.LoanAmount = 0;
            loanModel.Loan.LoanInterest = 20;
            loanModel.LoanInterestAmount = 0;
            loanModel.Loan.PaymentStartDate = DateTime.Now;
            loanModel.Loan.PaymentEndDate = DateTime.Now.AddDays(99);
            loanModel.Loan.EncodedBy = userName;
            loanModel.Loan.StatusID = (int)AdministrationHelper.LoanStatus.New;
            loanModel.LoanStatus = AdministrationHelper.LoanStatus.New.ToString();
            loanModel.Loan.IsActive = true;

            return loanModel;
        }
        
        public static List<LoanItemModel> GetLoanListModel(List<LoanItem> loanItems)
        {
            var modelList = new List<LoanItemModel>();

            foreach(var item in loanItems)
            {
                var model = new LoanItemModel();
                model.LoanItem = item;
                model.EditLoanStyle = item.LoanStatus == AdministrationHelper.LoanStatus.New.ToString()
                                        ? AdministrationHelper.StyleShownBlock : AdministrationHelper.StyleHidden;

                modelList.Add(model);
            }

            return modelList;
        }

        public static LoanAddEditModel GetLoanAddEditModel(DatabaseContext db, string documentNo, string branchCode, string userName)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;

            var loanAddEditModel = new LoanAddEditModel();

            var agents = db.Agents.Where(w => w.BranchCode == branchCode);
            var loanTypes = db.LoanTypes;
            var branches = db.Branches;
            var paymentTypes = db.PaymentTypes;

            loanAddEditModel.Branches = AdministrationHelper.GetBranchDropDownList(branches);
            loanAddEditModel.Agents = AdministrationHelper.GetAgentDropDownList(agents);
            loanAddEditModel.LoanTypes = AdministrationHelper.GetLoanTypeDropDownList(loanTypes);
            loanAddEditModel.ProceedTypes = AdministrationHelper.GetProceedTypeDropDownList();
            loanAddEditModel.PaymentTypes = AdministrationHelper.GetPaymentTypeDropDownList(paymentTypes);
            
            loanAddEditModel.DBCommand = string.IsNullOrEmpty(documentNo)
                ? AdministrationHelper.DBCommand.Add.ToString()
                : AdministrationHelper.DBCommand.Edit.ToString();

            loanAddEditModel.LoanModel = GetLoanModel(documentNo, userName, branchCode);
            
            return loanAddEditModel;
        }

        public static LoanModel GetLoanModel(string documentNo, string userName, string branchCode)
        {
            var loanModel = new LoanModel();

            loanModel = string.IsNullOrEmpty(documentNo)
                ? GetLoanModelDefaults(new DatabaseContext(), userName, branchCode)
                : GetLoanQueryModel(documentNo, loanModel);

            return loanModel;
        }

        public static LoanDetailsModel GetLoanDetailsModel(DatabaseContext db, string documentNo, string userName)
        {
            var loanDetails = new LoanDetailsModel();

            loanDetails.IsAdmin = AdministrationHelper.GetLoggedInUserRoleID(db, userName) == (int)AdministrationHelper.Role.Admin;
            loanDetails.Loan = GetLoanQueryModel(documentNo);
            loanDetails.LoanSchedule = GetLoanPaymentSchedule(documentNo);
            loanDetails.LoanDeductions = GetLoanDeductions(documentNo);
            loanDetails.LoanPayments = GetLoanPayments(documentNo);
            loanDetails.PaymentTypes = AdministrationHelper.GetPaymentTypeDropDownList(db.PaymentTypes);

            var chargeTypes = db.ChargeTypes.Where(w => w.IsActive);
            var existingChargeTypes = loanDetails.LoanDeductions.Select(s => s.ChargeTypeID);
            var newChargeTypes = chargeTypes.Where(w => !existingChargeTypes.Contains(w.ID));

            loanDetails.AddEditDeleteDeductionStyle = loanDetails.Loan.IsActive
                                                //&& loanDetails.Loan.StatusID == (int)AdministrationHelper.LoanStatus.New
                                                ? AdministrationHelper.StyleShownBlock : AdministrationHelper.StyleHidden;

            loanDetails.AddDeductionStyle = loanDetails.Loan.IsActive
                                                && loanDetails.Loan.StatusID == (int)AdministrationHelper.LoanStatus.New
                                                && newChargeTypes.Any()
                                                ? AdministrationHelper.StyleShownInline : AdministrationHelper.StyleHidden;

            loanDetails.EditDeductionStyle = loanDetails.Loan.IsActive
                                                && loanDetails.Loan.StatusID == (int)AdministrationHelper.LoanStatus.New
                                                && loanDetails.LoanDeductions.Any()
                                                ? AdministrationHelper.StyleShownInline : AdministrationHelper.StyleHidden;

            //loanDetails.GenerateScheduleStyle = AdministrationHelper.StyleHidden;
            loanDetails.GenerateScheduleStyle = loanDetails.Loan.IsActive
                                                && loanDetails.Loan.StatusID < (int)AdministrationHelper.LoanStatus.Closed
                                                //&& !loanDetails.LoanSchedule.Any()
                                                ? AdministrationHelper.StyleShownBlock : AdministrationHelper.StyleHidden;

            loanDetails.DeleteScheduleStyle = AdministrationHelper.StyleHidden;
            //loanDetails.DeleteScheduleStyle = loanDetails.Loan.IsActive
            //                                    && loanDetails.Loan.StatusID < (int)AdministrationHelper.LoanStatus.Closed
            //                                    && loanDetails.LoanSchedule.Any()
            //                                    && !loanDetails.LoanPayments.Any()
            //                                    ? AdministrationHelper.StyleShownBlock : AdministrationHelper.StyleHidden;

            return loanDetails;
        }

        public static LoanDeductionAddEditModel GetLoanDeductionAddEditModel(DatabaseContext db, string documentNo, string command)
        {
            var loanDeductionAddEditModel = new LoanDeductionAddEditModel();
            loanDeductionAddEditModel.LoanDeductions = new List<LoanDeductionModel>();

            var chargeTypes = db.ChargeTypes.Where(w => w.IsActive == true);
            var deductions = db.LoanDeductions.Where(w => w.DocumentNo == documentNo);
            var existingChargeTypes = deductions.Select(s => s.ChargeTypeID);

            if (command == AdministrationHelper.DBCommand.Add.ToString())
            {
                loanDeductionAddEditModel.DBCommand = AdministrationHelper.DBCommand.Add.ToString();

                var newChargeTypes = chargeTypes.Where(w => !existingChargeTypes.Contains(w.ID));

                foreach (var newType in newChargeTypes)
                {
                    var newModel = new LoanDeductionModel();
                    newModel.ChargeTypeName = newType.ChargeTypeName;
                    newModel.LoanDeduction = new LoanDeduction();
                    newModel.LoanDeduction.ChargeTypeID = newType.ID;
                    newModel.LoanDeduction.DocumentNo = documentNo;
                    newModel.LoanDeduction.Amount = 0;

                    loanDeductionAddEditModel.LoanDeductions.Add(newModel);
                }
 
            }
            else if (command == AdministrationHelper.DBCommand.Edit.ToString())
            {
                loanDeductionAddEditModel.DBCommand = AdministrationHelper.DBCommand.Edit.ToString();

                if (deductions.Any())
                {
                    

                    foreach (var deduction in deductions)
                    {
                        var model = new LoanDeductionModel();
                        model.ChargeTypeName = AdministrationHelper.GetChargeTypeName(deduction.ChargeTypeID);
                        model.LoanDeduction = deduction;

                        loanDeductionAddEditModel.LoanDeductions.Add(model);
                    }

                
                }
            }

            return loanDeductionAddEditModel;
        }

        public static PrintChequeModel GetPrintChequeModel(DatabaseContext db, string branchCode, string searchText, string userName)
        {
            var printChequeModel = new PrintChequeModel();

            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            printChequeModel.Banks = AdministrationHelper.GetBanks();
            printChequeModel.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            printChequeModel.SelectedBranchCode = branchCode;
            printChequeModel.SearchText = searchText;
            printChequeModel.PrintChequeItems = GetPrintChequeItems(branchCode, searchText);

            return printChequeModel;
        }

        public static CancelLoanModel GetCancelLoanModel(DatabaseContext db, string branchCode, string searchText, string userName)
        {
            var model = new CancelLoanModel();

            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            model.SelectedBranchCode = branchCode;
            model.SearchText = searchText;
            model.CancelLoanItems = GetCancellableLoans(branchCode, searchText);

            return model;
        }

        public static PrintContractModel GetPrintContractModel(DatabaseContext db, string userName, string branchCode, string searchText)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var printContractModel = new PrintContractModel();
            printContractModel.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            printContractModel.SelectedBranchCode = branchCode;
            printContractModel.PrintContractItems = GetReleaseToBranchList(branchCode, searchText);

            return printContractModel;

        }

        public static ReleaseToBranchModel GetReleaseToBranchModel(DatabaseContext db, string branchCode, string searchText, string userName)
        {

            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var model = new ReleaseToBranchModel();
            model.SearchText = searchText;
            model.SelectedBranchCode = branchCode;
            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            model.ReleaseToBranchItems = FundingHelper.GetReleaseToBranchList(model.SelectedBranchCode, searchText);

            return model;
        }

        public static ReleaseToCustomerModel GetReleaseToCustomerModel(DatabaseContext db, string branchCode, string searchText, string userName)
        {

            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var model = new ReleaseToCustomerModel();

            model.IsAdmin = AdministrationHelper.GetLoggedInUserRoleID(db, userName) == (int)AdministrationHelper.Role.Admin;
            model.SearchText = searchText;
            model.SelectedBranchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            model.ReleaseToCustomerItems = FundingHelper.GetReleaseToCustomerList(model.SelectedBranchCode, searchText);

            return model;
        }

        public static ContractModel GetContractModel(string documentNo)
        {
            var queryModel = new ContractModel();

            using (var db = new DatabaseContext())
            {
                var paramDocumentNo = new SqlParameter("@DocumentNo", documentNo);

                queryModel = db.Database
                    .SqlQuery<ContractModel>("sp_GetLoanForContractPrinting @DocumentNo", paramDocumentNo)
                    .SingleOrDefault<ContractModel>();

                queryModel.FinanceChargeTypes = db.ChargeTypes.Where(w => w.IsActive == true && w.SortID == (int)AdministrationHelper.ChargeTypeSort.Finance).ToList();
                queryModel.NonFinanceChargeTypes = db.ChargeTypes.Where(w => w.IsActive == true && w.SortID == (int)AdministrationHelper.ChargeTypeSort.NonFinance).ToList();

                queryModel.Deductions = new List<LoanDeduction>();
                queryModel.Deductions = db.LoanDeductions.Where(w => w.DocumentNo == documentNo).ToList();
                
                queryModel.Collaterals = new List<LoanCollateral>();
                queryModel.Collaterals = db.LoanCollaterals.Where(w => w.DocumentNo == documentNo).ToList();

                queryModel.CoMakers = new List<LoanCoMaker>();
                queryModel.CoMakers = db.LoanCoMakers.Where(w => w.DocumentNo == documentNo).ToList();
            }

            return queryModel;
        }

        public static GenerateNewScheduleModel GetGenerateScheduleModel(DatabaseContext db, string documentNo, string userName, string source)
        {
            var loan = db.Loans.Where(w => w.DocumentNo == documentNo).SingleOrDefault<Loan>();
            var scheds = db.LoanPaymentSchedules.Where(w => w.DocumentNo == documentNo);

            var model = new GenerateNewScheduleModel();
            model.Source = source;
            model.GenerateScheduleStyle = scheds.Any() ? AdministrationHelper.StyleHidden : AdministrationHelper.StyleShownInline;
            model.DeleteScheduleStyle = scheds.Any() ? AdministrationHelper.StyleShownInline : AdministrationHelper.StyleHidden;
            model.DocumentNo = documentNo;
            model.DocumentDate = loan.DocumentDate;
            model.OldPaymentStartDate = loan.PaymentStartDate;
            model.OldPaymentEndDate = loan.PaymentEndDate;
            model.NewPaymentStartDate = DateTime.Today;
            model.Term = loan.Term;
            model.ProceedTypeID = loan.ProceedTypeID;
            model.ProceedTypeName = AdministrationHelper.GetProceedTypeName(loan.ProceedTypeID);
            model.Schedule = new List<LoanPaymentSchedule>();
            model.Schedule = scheds.Any() ? scheds.ToList() : new List<LoanPaymentSchedule>();
            model.IsAdmin = AdministrationHelper.GetLoggedInUserRoleID(db, userName) == (int)AdministrationHelper.Role.Admin;

            return model;
            
        }

        public static LoanJournalModel GetLoanJournalModel(DatabaseContext db, string documentNo)
        {
            var model = new LoanJournalModel();

            var loan = db.Loans.Where(w => w.DocumentNo == documentNo).SingleOrDefault<Loan>();

            model.DocumentNo = documentNo;
            model.BorrowerName = AdministrationHelper.GetBorrowerName(loan.BorrowerID);
            model.Remarks = string.Empty;
            model.LoanJournals = db.LoanJourals.Where(w => w.DocumentNo == documentNo).ToList();

            return model;

        }

        public static LoanPaymentAddEditModel GetLoanPaymentAddEditModel(DatabaseContext db, int? id, string documentNo)
        {
            var model = new LoanPaymentAddEditModel();

            model.PaymentTypes = AdministrationHelper.GetPaymentTypeDropDownList(db.PaymentTypes);
            model.DBCommand = id.HasValue ? AdministrationHelper.DBCommand.Edit.ToString() : AdministrationHelper.DBCommand.Add.ToString();
            model.LoanPayment = id.HasValue 
                                ? db.LoanPayments.Where(w => w.ID == id).SingleOrDefault() 
                                : new LoanPayment()
                                        {
                                            DocumentNo = documentNo,
                                            PaymentDate = DateTime.Today,
                                            StatusID = (int)AdministrationHelper.LoanPaymentStatus.Posted
                                        };

            return model;

        }

        public static LoanPaymentHistoryModel GetLoanPaymentHistoryModel(DatabaseContext db, string documentNo)
        {
            var model = new LoanPaymentHistoryModel();
            var loan = GetLoanQueryModel(documentNo);

            model.DocumentNo = documentNo;
            model.DocumentDate = loan.DocumentDate;
            model.BorrowerName = AdministrationHelper.GetBorrowerName(loan.BorrowerID);
            model.PaymentStartDate = loan.PaymentStartDate.GetValueOrDefault();
            model.PaymentEndDate = loan.PaymentEndDate;
            model.ProceedTypeName = AdministrationHelper.GetProceedTypeName(loan.ProceedTypeID);
            model.Receivable = loan.Receivable;
            model.TotalPayment = loan.TotalPayment;
            model.OutstandingBalance = loan.OutstandingBalance;

            model.ScheduleVsPaymentModel = GetScheduleVsPaymentModel(db, loan, (AdministrationHelper.ProceedType)loan.ProceedTypeID);

            return model;

        }

        public static List<ScheduleVsPaymentModel> GetAdvancePayment(DatabaseContext db, List<ScheduleVsPaymentModel> itemList, LoanQueryModel loan)
        {
            decimal advancePayment = 0;
            var advancePaymentList = db.LoanDeductions.Where(w => w.DocumentNo == loan.DocumentNo && w.ChargeTypeID == 4);
            if (advancePaymentList.Any())
            {
                advancePayment = advancePaymentList.SingleOrDefault().Amount;
                itemList.Add(new ScheduleVsPaymentModel()
                {
                    DocumentNo = loan.DocumentNo,
                    DueDate = loan.DocumentDate,
                    DueAmount = 0,
                    PaymentDate = loan.DocumentDate,
                    PaidAmount = advancePayment,
                    Penalty = 0,
                    PaymentStatus = string.Empty,
                    PaymentType = "ADVANCE PAYMENT"
                });
            }

            return itemList;
        }

        public static decimal GetPaidAmount(DateTime startDate, DateTime dueDate, List<LoanPayment> payments, bool IsPenalty = false)
        {
            decimal amount = 0;
            decimal penalty = 0;

            amount = payments.Where(w => w.PaymentDate > startDate && w.PaymentDate <= dueDate).Sum(s => s.Amount);
            penalty = payments.Where(w => w.PaymentDate > startDate && w.PaymentDate <= dueDate).Sum(s => s.Penalty);

            return IsPenalty ? penalty : amount;
        }

        public static List<ScheduleVsPaymentModel> GetScheduleVsPaymentModel(DatabaseContext db, LoanQueryModel loan, AdministrationHelper.ProceedType proceedType)
        {
            var payments = db.LoanPayments.Where(w => w.DocumentNo == loan.DocumentNo && !w.IsVoid && w.StatusID == (int)AdministrationHelper.LoanPaymentStatus.Posted).ToList();
            var schedule = GetSchedule(db, loan.DocumentNo, proceedType, payments.ToList());
            var itemList = new List<ScheduleVsPaymentModel>();

            itemList = GetAdvancePayment(db, itemList, loan);

            var scheduledDates = schedule.Select(w => w.DueDate).ToList();

            for (var i = 0; i < schedule.Count; i++)
            {
                DateTime startDate = i > 0 ? schedule[i - 1].DueDate : schedule[i].DueDate.AddDays(-1);
                var item = new ScheduleVsPaymentModel();

                item.DocumentNo = schedule[i].DocumentNo;
                item.DueDate = schedule[i].DueDate;
                item.DueAmount = schedule[i].DueAmount;
                item.PaymentDate = schedule[i].DueDate;
                item.PaidAmount = GetPaidAmount(startDate, schedule[i].DueDate, payments);
                item.Penalty = GetPaidAmount(startDate, schedule[i].DueDate, payments, IsPenalty: true);
                item.PaymentType = AdministrationHelper.GetPaymentTypeName(loan.PaymentTypeID);
                item.PaymentStatus = AdministrationHelper.LoanPaymentStatus.Posted.ToString();

                itemList.Add(item);
            }

            var paymentsAfterMaturity = payments.Where(w => w.PaymentDate > loan.PaymentEndDate);

            foreach (var pay in paymentsAfterMaturity)
            {
                var item = new ScheduleVsPaymentModel();

                item.DocumentNo = pay.DocumentNo;
                item.DueDate = loan.PaymentEndDate.GetValueOrDefault();
                item.DueAmount = loan.OutstandingBalance;
                item.PaymentDate = pay.PaymentDate;
                item.PaidAmount = pay.Amount;
                item.Penalty = pay.Penalty;
                item.PaymentType = AdministrationHelper.GetPaymentTypeName(pay.PaymentTypeID);
                item.PaymentStatus = AdministrationHelper.GetLoanPaymentStatus(pay.StatusID);

                itemList.Add(item);
            }

            return itemList;
        }

        public static List<LoanPaymentSchedule> GetSchedule(DatabaseContext db, string documentNo, AdministrationHelper.ProceedType proceedType, List<LoanPayment> payments)
        {
            var loan = GetLoanQueryModel(documentNo);

            DateTime lastPaymentDate = DateTime.Now;

            var schedule = db.LoanPaymentSchedules.Where(w => w.DocumentNo == documentNo && w.DueDate <= lastPaymentDate).ToList();
            var scheds = new List<LoanPaymentSchedule>();

            switch (proceedType)
            {
                case AdministrationHelper.ProceedType.Weekly:
                    {
                        var nextDate = loan.PaymentStartDate;
                        var dueAmount = loan.Receivable / 100 * 7;

                        foreach (var item in schedule)
                        {
                            var sched = new LoanPaymentSchedule();

                            if (item.DueDate == nextDate || nextDate > loan.PaymentEndDate)
                            {
                                sched.DocumentNo = item.DocumentNo;
                                sched.DueDate = item.DueDate;
                                sched.DueAmount = dueAmount;
                                sched.ID = item.ID;
                                scheds.Add(sched);

                                nextDate = item.DueDate.AddDays(7);
                            }
                        }

                        break;
                    }
                case AdministrationHelper.ProceedType.SemiMonthly:
                    {
                        var semiMonthDate1 = loan.PaymentStartDate.GetValueOrDefault().Date;
                        var semiMonthDate2 = loan.PaymentStartDate.GetValueOrDefault().AddDays(15).Date;

                        var dueAmount = loan.Receivable / 100 * 15;

                        foreach (var item in schedule)
                        {
                            var sched = new LoanPaymentSchedule();

                            if (item.DueDate.Date.Equals(semiMonthDate1) || item.DueDate.Date.Equals(semiMonthDate2))
                            {
                                sched.DocumentNo = item.DocumentNo;
                                sched.DueDate = item.DueDate;
                                sched.DueAmount = dueAmount;
                                sched.ID = item.ID;
                                scheds.Add(sched);
                            }
                        }

                        break;
                    }
                case AdministrationHelper.ProceedType.Monthly:
                    {
                        var monthlyDate = loan.PaymentStartDate.GetValueOrDefault().Date;
                        var dueAmount = loan.Receivable / 100 * 30;

                        foreach (var item in schedule)
                        {
                            var sched = new LoanPaymentSchedule();

                            if (item.DueDate.Date.Equals(monthlyDate))
                            {
                                sched.DocumentNo = item.DocumentNo;
                                sched.DueDate = item.DueDate;
                                sched.DueAmount = dueAmount;
                                sched.ID = item.ID;
                                scheds.Add(sched);
                            }
                        }

                        break;
                    }
                default:
                    {
                        scheds = schedule;
                        break;
                    }
            }

            return scheds;
        }

        #region Excel

        public static ExcelWorksheet SetWorksheetProtection(ExcelWorksheet worksheet)
        {
            worksheet.Protection.AllowAutoFilter =
            worksheet.Protection.AllowDeleteColumns =
            worksheet.Protection.AllowDeleteRows =
            worksheet.Protection.AllowEditObject =
            worksheet.Protection.AllowEditScenarios =
            worksheet.Protection.AllowFormatCells =
            worksheet.Protection.AllowFormatColumns =
            worksheet.Protection.AllowFormatRows =
            worksheet.Protection.AllowInsertColumns =
            worksheet.Protection.AllowInsertHyperlinks =
            worksheet.Protection.AllowInsertRows =
            worksheet.Protection.AllowPivotTables =
            worksheet.Protection.AllowSelectLockedCells =
            worksheet.Protection.AllowSelectUnlockedCells =
            worksheet.Protection.AllowSort = false;

            worksheet.Protection.IsProtected = true;
            worksheet.Protection.SetPassword("kensilang");

            return worksheet;
        }

        #endregion

        #region Query Models

        public static List<LoanItem> GetLoanList(string branchCode, string searchText)
        {
            var loans = new List<LoanItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramSearchText = new SqlParameter("@SearchText", searchText);

                loans = db.Database
                    .SqlQuery<LoanItem>("sp_GetLoanList @BranchCode, @SearchText", paramBranchCode, paramSearchText)
                    .ToList();
            }

            return loans;

        }

        public static LoanQueryModel GetLoanQueryModel(string documentNo)
        {
            var queryModel = new LoanQueryModel();

            using (var db = new DatabaseContext())
            {
                var paramDocumentNo = new SqlParameter("@DocumentNo", documentNo);

                queryModel = db.Database
                    .SqlQuery<LoanQueryModel>("sp_GetLoan @DocumentNo", paramDocumentNo)
                    .SingleOrDefault<LoanQueryModel>();
            }

            return queryModel;

        }

        public static LoanModel GetLoanQueryModel(string documentNo, LoanModel loanModel)
        {
            var loanQueryModel = GetLoanQueryModel(documentNo);
            loanModel.Loan = new Loan();

            loanModel.Loan.AgentID = loanQueryModel.AgentID;
            loanModel.AgentName = loanQueryModel.AgentName;
            loanModel.Loan.BorrowerID = loanQueryModel.BorrowerID;
            loanModel.BorrowerName = loanQueryModel.BorrowerName;
            loanModel.Loan.BranchCode = loanQueryModel.BranchCode;
            loanModel.BranchName = loanQueryModel.BranchName;
            loanModel.Loan.CreditLimit = loanQueryModel.CreditLimit;
            loanModel.Loan.Term = loanQueryModel.Term;
            loanModel.Loan.DocumentDate = loanQueryModel.DocumentDate;
            loanModel.Loan.DocumentNo = loanQueryModel.DocumentNo;
            loanModel.Loan.EncodedBy = loanQueryModel.EncodedBy;
            loanModel.Loan.IsActive = loanQueryModel.IsActive;
            loanModel.Loan.LoanAmount = loanQueryModel.LoanAmount;
            loanModel.Loan.LoanInterest = loanQueryModel.LoanInterest;
            loanModel.Loan.LoanTypeID = loanQueryModel.LoanTypeID;
            loanModel.Loan.PaymentTypeID = loanQueryModel.PaymentTypeID;
            loanModel.PaymentTypeName = loanQueryModel.PaymentTypeName;
            loanModel.Loan.PaymentEndDate = loanQueryModel.PaymentEndDate;
            loanModel.Loan.PaymentStartDate = loanQueryModel.PaymentStartDate;
            loanModel.Loan.ProceedTypeID = loanQueryModel.ProceedTypeID;
            loanModel.Loan.StatusID = loanQueryModel.StatusID;
            loanModel.LoanInterestAmount = loanQueryModel.LoanInterestAmount;
            loanModel.LoanStatus = loanQueryModel.LoanStatus;
            loanModel.LoanTypeName = loanQueryModel.LoanTypeName;
            loanModel.ProceedTypeName = loanQueryModel.ProceedTypeName;
            loanModel.Loan.PaymentSecondDate = loanQueryModel.PaymentSecondDate;
            
            return loanModel;
        }

        public static List<LoanPaymentScheduleQueryModel> GetLoanPaymentSchedule(string documentNo)
        {
            var queryModel = new List<LoanPaymentScheduleQueryModel>();

            using (var db = new DatabaseContext())
            {
                var paramDocumentNo = new SqlParameter("@DocumentNo", documentNo);

                queryModel = db.Database
                    .SqlQuery<LoanPaymentScheduleQueryModel>("sp_GetLoanPaymentSchedule @DocumentNo", paramDocumentNo)
                    .ToList();
            }

            return queryModel;

        }

        public static List<LoanDeductionQueryModel> GetLoanDeductions(string documentNo)
        {
            var queryModel = new List<LoanDeductionQueryModel>();

            using (var db = new DatabaseContext())
            {
                var paramDocumentNo = new SqlParameter("@DocumentNo", documentNo);

                queryModel = db.Database
                    .SqlQuery<LoanDeductionQueryModel>("sp_GetLoanDeductions @DocumentNo", paramDocumentNo)
                    .ToList();
            }

            return queryModel;

        }

        public static List<LoanPaymentQueryModel> GetLoanPayments(string documentNo)
        {
            var queryModel = new List<LoanPaymentQueryModel>();

            using (var db = new DatabaseContext())
            {
                var paramDocumentNo = new SqlParameter("@DocumentNo", documentNo);

                queryModel = db.Database
                    .SqlQuery<LoanPaymentQueryModel>("sp_GetLoanPayments @DocumentNo", paramDocumentNo)
                    .ToList();
            }

            return queryModel;

        }

        public static List<ReleaseToBranchItem> GetReleaseToBranchList(string branchCode, string searchText)
        {
            var releaseToBranchItems = new List<ReleaseToBranchItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramSearchText = new SqlParameter("@SearchText", searchText);

                releaseToBranchItems = db.Database
                    .SqlQuery<ReleaseToBranchItem>("sp_GetReleaseToBranchList @BranchCode, @SearchText", paramBranchCode, paramSearchText)
                    .ToList();
            }

            return releaseToBranchItems;
        }

        public static List<ReleaseToCustomerItem> GetReleaseToCustomerList(string branchCode, string searchText)
        {
            var releaseToCustomerItems = new List<ReleaseToCustomerItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramSearchText = new SqlParameter("@SearchText", searchText);

                releaseToCustomerItems = db.Database
                    .SqlQuery<ReleaseToCustomerItem>("sp_GetReleaseToCustomerList @BranchCode, @SearchText", paramBranchCode, paramSearchText)
                    .ToList();
            }

            return releaseToCustomerItems;
        }

        public static List<PrintChequeQueryModel> GetPrintChequeItems(string branchCode, string searchText)
        {
            var printChequeItems = new List<PrintChequeQueryModel>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramSearchText = new SqlParameter("@SearchText", searchText);

                printChequeItems = db.Database
                    .SqlQuery<PrintChequeQueryModel>("sp_GetLoansForChequePrinting @BranchCode, @SearchText", paramBranchCode, paramSearchText)
                    .ToList();
            }

            return printChequeItems;
        }

        public static List<CancelLoanQueryModel> GetCancellableLoans(string branchCode, string searchText)
        {
            var items = new List<CancelLoanQueryModel>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramSearchText = new SqlParameter("@SearchText", searchText);

                items = db.Database
                    .SqlQuery<CancelLoanQueryModel>("sp_GetCancellableLoans @BranchCode, @SearchText", paramBranchCode, paramSearchText)
                    .ToList();
            }

            return items;
        }

        #endregion

        #region Decimal to Words Converter

        public static string ConvertDecimalToWords(string input)
        {
            string decimals = "";

            if (input.Contains("."))
            {
                decimals = input.Substring(input.IndexOf(".") + 1);
                // remove decimal part from input
                input = input.Remove(input.IndexOf("."));
            }

            // Convert input into words. save it into strWords
            string strWords = GetWords(input) + " PESOS";


            //if (decimals.Length > 0)
            //{
            //    // if there is any decimal part convert it to words and add it to strWords.
            //    strWords += " AND " + decimals + "/100";
            //}

            return strWords.ToUpper();
        }

        private static string GetWords(string input)
        {
            // these are seperators for each 3 digit in numbers. you can add more if you want convert beigger numbers.
            string[] seperators = { "", " Thousand ", " Million ", " Billion " };

            // Counter is indexer for seperators. each 3 digit converted this will count.
            int i = 0;

            string strWords = "";

            while (input.Length > 0)
            {
                // get the 3 last numbers from input and store it. if there is not 3 numbers just use take it.
                string _3digits = input.Length < 3 ? input : input.Substring(input.Length - 3);
                // remove the 3 last digits from input. if there is not 3 numbers just remove it.
                input = input.Length < 3 ? "" : input.Remove(input.Length - 3);

                int no = int.Parse(_3digits);
                // Convert 3 digit number into words.
                _3digits = GetWord(no);

                // apply the seperator.
                _3digits += seperators[i];
                // since we are getting numbers from right to left then we must append resault to strWords like this.
                strWords = _3digits + strWords;

                // 3 digits converted. count and go for next 3 digits
                i++;
            }
            return strWords;
        }

        // convert 3digit number into words.
        private static string GetWord(int no)
        {
            string[] Ones =
        {
            "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven",
            "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen"
        };

            string[] Tens = { "Ten", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety" };

            string word = "";

            if (no > 99 && no < 1000)
            {
                int i = no / 100;
                word = word + Ones[i - 1] + " Hundred ";
                no = no % 100;
            }

            if (no > 19 && no < 100)
            {
                int i = no / 10;
                word = word + Tens[i - 1] + " ";
                no = no % 10;
            }

            if (no > 0 && no < 20)
            {
                word = word + Ones[no - 1];
            }

            return word;
        }

        #endregion

    }
}